﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class VendorDA : IVendorDA
    {
        private string ADDRESS = "@ADDRESS";
        private string B_ADDRESS = "@B_ADDRESS";
        private string B_CITY = "@B_CITY";
        private string B_CONTACT1 = "@B_CONTACT1";
        private string B_CONTACT2 = "@B_CONTACT2";
        private string B_COUNTRY = "@B_COUNTRY";
        private string B_COUNTRY_CODE = "@B_COUNTRY_CODE";
        private string B_EMAIL1 = "@B_EMAIL1";
        private string B_EMAIL2 = "@B_EMAIL2";
        private string B_FAX1 = "@B_FAX1";
        private string B_FAX2 = "@B_FAX2";
        private string B_PHONE1 = "@B_PHONE1";
        private string B_PHONE2 = "@B_PHONE2";
        private string B_STATE = "@B_STATE";
        private string B_ZIP = "@B_ZIP";
        private string CITY = "@CITY";
        private string COMPANY = "@COMPANY";
        private string CONTACT1 = "@CONTACT1";
        private string CONTACT2 = "@CONTACT2";
        private string COUNTRY = "@COUNTRY";
        private string COUNTRY_CODE = "@COUNTRY_CODE";
        private string CREATED_BY = "@CREATED_BY";
        private string DESCRIPTION = "@DESCRIPTION";
        private string EMAIL1 = "@EMAIL1";
        private string EMAIL2 = "@EMAIL2";
        private string FAX1 = "@FAX1";
        private string FAX2 = "@FAX2";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string PHONE1 = "@PHONE1";
        private string PHONE2 = "@PHONE2";
        private string REMARK = "@REMARK";
        private string SQL_CHECK_AUTHORIZATION_VENDOR_USERID = " SELECT COUNT(1) FROM @_@VENDOR V INNER JOIN    sysadm.USER_VENDOR UV ON V.VENDOR_ID=UV.VENDOR_ID AND UV.USER_ID=@USER_ID WHERE V.VENDOR_ID = @VENDOR_ID ";
        private string SQL_CHECK_VENDOR_ID_UNIQUE = " SELECT COUNT(1) FROM @_@VENDOR V WHERE V.VENDOR_ID = @VENDOR_ID ";
        private string SQL_DELETE_VENDOR = " DELETE FROM @_@VENDOR WHERE VENDOR_ID = @VENDOR_ID ";
        private string SQL_INSERT_VENDOR = " INSERT INTO @_@VENDOR ( WH_ID, VENDOR_ID, COMPANY, DESCRIPTION, VAT, CITY, STATE, ZIP, COUNTRY, COUNTRY_CODE, ADDRESS, CONTACT1, CONTACT2, PHONE1, PHONE2, FAX1, FAX2, EMAIL1, EMAIL2, B_CITY, B_STATE, B_ZIP, B_COUNTRY, B_COUNTRY_CODE, B_ADDRESS, B_CONTACT1, B_CONTACT2, B_PHONE1, B_PHONE2, B_FAX1, B_FAX2, B_EMAIL1, B_EMAIL2, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @VENDOR_ID, @COMPANY, @DESCRIPTION, @VAT, @CITY, @STATE, @ZIP, @COUNTRY, @COUNTRY_CODE, @ADDRESS, @CONTACT1, @CONTACT2, @PHONE1, @PHONE2, @FAX1, @FAX2, @EMAIL1, @EMAIL2, @B_CITY, @B_STATE, @B_ZIP, @B_COUNTRY, @B_COUNTRY_CODE, @B_ADDRESS, @B_CONTACT1, @B_CONTACT2, @B_PHONE1, @B_PHONE2, @B_FAX1, @B_FAX2, @B_EMAIL1, @B_EMAIL2, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_VENDOR = " SELECT  V.ROWID, V.WH_ID, V.VENDOR_ID, V.COMPANY, V.DESCRIPTION, V.VAT, V.CITY, V.STATE, V.ZIP, V.COUNTRY, V.COUNTRY_CODE, V.ADDRESS, V.CONTACT1, V.CONTACT2, V.PHONE1, V.PHONE2, V.FAX1, V.FAX2, V.EMAIL1, V.EMAIL2, V.B_CITY, V.B_STATE, V.B_ZIP, V.B_COUNTRY, V.B_COUNTRY_CODE, V.B_ADDRESS, V.B_CONTACT1, V.B_CONTACT2, V.B_PHONE1, V.B_PHONE2, V.B_FAX1, V.B_FAX2, V.B_EMAIL1, V.B_EMAIL2, V.UDF1, V.UDF2, V.UDF3, V.UDF4, V.UDF5, V.UDF6, V.UDF7, V.UDF8, V.UDF9, V.UDF10, V.REMARK, V.IS_ACTIVE, V.TS, V.CREATED_BY, V.CREATED_DATE, V.UPDATED_BY, V.UPDATED_DATE    FROM  @_@VENDOR V INNER JOIN SYSADM.USER_VENDOR UV ON V.VENDOR_ID = UV.VENDOR_ID AND UV.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_VENDOR = " UPDATE @_@VENDOR SET WH_ID = @WH_ID, COMPANY = @COMPANY, DESCRIPTION = @DESCRIPTION, VAT = @VAT, CITY = @CITY, STATE = @STATE, ZIP = @ZIP, COUNTRY = @COUNTRY, COUNTRY_CODE = @COUNTRY_CODE, ADDRESS = @ADDRESS, CONTACT1 = @CONTACT1, CONTACT2 = @CONTACT2, PHONE1 = @PHONE1, PHONE2 = @PHONE2, FAX1 = @FAX1, FAX2 = @FAX2, EMAIL1 = @EMAIL1, EMAIL2 = @EMAIL2, B_CITY = @B_CITY, B_STATE = @B_STATE, B_ZIP = @B_ZIP, B_COUNTRY = @B_COUNTRY, B_COUNTRY_CODE = @B_COUNTRY_CODE, B_ADDRESS = @B_ADDRESS, B_CONTACT1 = @B_CONTACT1, B_CONTACT2 = @B_CONTACT2, B_PHONE1 = @B_PHONE1, B_PHONE2 = @B_PHONE2, B_FAX1 = @B_FAX1, B_FAX2 = @B_FAX2, B_EMAIL1 = @B_EMAIL1, B_EMAIL2 = @B_EMAIL2, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE VENDOR_ID = @VENDOR_ID  ";
        private string STATE = "@STATE";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VAT = "@VAT";
        private string VENDOR_ID = "@VENDOR_ID";
        private string WH_ID = "@WH_ID";
        private string ZIP = "@ZIP";

        public bool CheckAuthorizationVendorIDUserID(string vendorID, string whLoginID, PubParasInfo pubParasInfo)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VENDOR_ID", vendorID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_AUTHORIZATION_VENDOR_USERID.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckAuthorizationVendorIDUserID(DataBase dataBase, DbTransaction tran, string vendorID, string whLoginID, PubParasInfo pubParasInfo)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VENDOR_ID", vendorID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_AUTHORIZATION_VENDOR_USERID.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckVendorIDUnique(string vendorID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VENDOR_ID", vendorID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_VENDOR_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckVendorIDUnique(DataBase dataBase, DbTransaction tran, string vendorID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VENDOR_ID", vendorID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_VENDOR_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteVendor(List<string> vendorID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < vendorID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VENDOR_ID", vendorID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_VENDOR.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteVendor(DataBase dataBase, DbTransaction tran, List<string> vendorID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < vendorID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VENDOR_ID", vendorID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_VENDOR.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllVendor(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_VENDOR.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllVendor(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_VENDOR.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public VendorInfo GetVendorByID(string vendorID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_VENDOR.Replace("@_@", whLoginID) + " WHERE V.VENDOR_ID = @VENDOR_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VENDOR_ID", vendorID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            VendorInfo vendorInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    vendorInfo = new VendorInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["COMPANY"].ToString(), reader["DESCRIPTION"].ToString(), reader["VAT"].ToString(), reader["CITY"].ToString(), reader["STATE"].ToString(), reader["ZIP"].ToString(), reader["COUNTRY"].ToString(), reader["COUNTRY_CODE"].ToString(), reader["ADDRESS"].ToString(), reader["CONTACT1"].ToString(), reader["CONTACT2"].ToString(), reader["PHONE1"].ToString(), reader["PHONE2"].ToString(), reader["FAX1"].ToString(), reader["FAX2"].ToString(), reader["EMAIL1"].ToString(), reader["EMAIL2"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return vendorInfo;
        }

        public VendorInfo GetVendorByID(DataBase dataBase, DbTransaction tran, string vendorID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_VENDOR.Replace("@_@", whLoginID) + " WHERE V.VENDOR_ID = @VENDOR_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VENDOR_ID", vendorID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            VendorInfo vendorInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                vendorInfo = new VendorInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["COMPANY"].ToString(), reader["DESCRIPTION"].ToString(), reader["VAT"].ToString(), reader["CITY"].ToString(), reader["STATE"].ToString(), reader["ZIP"].ToString(), reader["COUNTRY"].ToString(), reader["COUNTRY_CODE"].ToString(), reader["ADDRESS"].ToString(), reader["CONTACT1"].ToString(), reader["CONTACT2"].ToString(), reader["PHONE1"].ToString(), reader["PHONE2"].ToString(), reader["FAX1"].ToString(), reader["FAX2"].ToString(), reader["EMAIL1"].ToString(), reader["EMAIL2"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return vendorInfo;
        }

        public DataSet GetVendorByQueryList(List<string> sqlWhere, VendorQueryEntity vendorQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_VENDOR.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!vendorQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, vendorQuery.CurrentPage, vendorQuery.PageSize, vendorQuery.SortField, vendorQuery.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetVendorByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, VendorQueryEntity vendorQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_VENDOR.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!vendorQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, vendorQuery.CurrentPage, vendorQuery.PageSize, vendorQuery.SortField, vendorQuery.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertVendor(VendorInfo vendorInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    vendorInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Vendor_Parameters(vendorInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_VENDOR.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertVendor(DataBase dataBase, DbTransaction tran, VendorInfo vendorInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    vendorInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Vendor_Parameters(vendorInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_VENDOR.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Vendor_Parameters(VendorInfo vendorInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), new SqlParameter(this.COMPANY, SqlDbType.VarChar, 50), new SqlParameter(this.DESCRIPTION, SqlDbType.VarChar, 50), new SqlParameter(this.VAT, SqlDbType.VarChar, 20), new SqlParameter(this.CITY, SqlDbType.VarChar, 50), new SqlParameter(this.STATE, SqlDbType.VarChar, 20), new SqlParameter(this.ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.PHONE1, SqlDbType.VarChar, 20), new SqlParameter(this.PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.FAX1, SqlDbType.VarChar, 20), 
                new SqlParameter(this.FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.EMAIL1, SqlDbType.VarChar, 60), new SqlParameter(this.EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.B_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.B_STATE, SqlDbType.VarChar, 20), new SqlParameter(this.B_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.B_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.B_COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.B_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.B_CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.B_CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.B_PHONE1, SqlDbType.VarChar, 20), new SqlParameter(this.B_PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX1, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.B_EMAIL1, SqlDbType.VarChar, 60), 
                new SqlParameter(this.B_EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x7d0), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(vendorInfo.WhID))
            {
                paramArray[0].Value = vendorInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = vendorInfo.VendorID;
            if (!string.IsNullOrEmpty(vendorInfo.Company))
            {
                paramArray[2].Value = vendorInfo.Company;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Description))
            {
                paramArray[3].Value = vendorInfo.Description;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Vat))
            {
                paramArray[4].Value = vendorInfo.Vat;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.City))
            {
                paramArray[5].Value = vendorInfo.City;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.State))
            {
                paramArray[6].Value = vendorInfo.State;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Zip))
            {
                paramArray[7].Value = vendorInfo.Zip;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Country))
            {
                paramArray[8].Value = vendorInfo.Country;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.CountryCode))
            {
                paramArray[9].Value = vendorInfo.CountryCode;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Address))
            {
                paramArray[10].Value = vendorInfo.Address;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Contact1))
            {
                paramArray[11].Value = vendorInfo.Contact1;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Contact2))
            {
                paramArray[12].Value = vendorInfo.Contact2;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Phone1))
            {
                paramArray[13].Value = vendorInfo.Phone1;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Phone2))
            {
                paramArray[14].Value = vendorInfo.Phone2;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Fax1))
            {
                paramArray[15].Value = vendorInfo.Fax1;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Fax2))
            {
                paramArray[0x10].Value = vendorInfo.Fax2;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Email1))
            {
                paramArray[0x11].Value = vendorInfo.Email1;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Email2))
            {
                paramArray[0x12].Value = vendorInfo.Email2;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BCity))
            {
                paramArray[0x13].Value = vendorInfo.BCity;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BState))
            {
                paramArray[20].Value = vendorInfo.BState;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BZip))
            {
                paramArray[0x15].Value = vendorInfo.BZip;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BCountry))
            {
                paramArray[0x16].Value = vendorInfo.BCountry;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BCountryCode))
            {
                paramArray[0x17].Value = vendorInfo.BCountryCode;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BAddress))
            {
                paramArray[0x18].Value = vendorInfo.BAddress;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BContact1))
            {
                paramArray[0x19].Value = vendorInfo.BContact1;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BContact2))
            {
                paramArray[0x1a].Value = vendorInfo.BContact2;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BPhone1))
            {
                paramArray[0x1b].Value = vendorInfo.BPhone1;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BPhone2))
            {
                paramArray[0x1c].Value = vendorInfo.BPhone2;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BFax1))
            {
                paramArray[0x1d].Value = vendorInfo.BFax1;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BFax2))
            {
                paramArray[30].Value = vendorInfo.BFax2;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BEmail1))
            {
                paramArray[0x1f].Value = vendorInfo.BEmail1;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.BEmail2))
            {
                paramArray[0x20].Value = vendorInfo.BEmail2;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Udf1))
            {
                paramArray[0x21].Value = vendorInfo.Udf1;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Udf2))
            {
                paramArray[0x22].Value = vendorInfo.Udf2;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Udf3))
            {
                paramArray[0x23].Value = vendorInfo.Udf3;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Udf4))
            {
                paramArray[0x24].Value = vendorInfo.Udf4;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Udf5))
            {
                paramArray[0x25].Value = vendorInfo.Udf5;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Udf6))
            {
                paramArray[0x26].Value = vendorInfo.Udf6;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Udf7))
            {
                paramArray[0x27].Value = vendorInfo.Udf7;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Udf8))
            {
                paramArray[40].Value = vendorInfo.Udf8;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Udf9))
            {
                paramArray[0x29].Value = vendorInfo.Udf9;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Udf10))
            {
                paramArray[0x2a].Value = vendorInfo.Udf10;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vendorInfo.Remark))
            {
                paramArray[0x2b].Value = vendorInfo.Remark;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            paramArray[0x2c].Value = vendorInfo.IsActive;
            paramArray[0x2d].Value = vendorInfo.CreatedBy;
            paramArray[0x2e].Value = vendorInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateVendor(VendorInfo vendorInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    vendorInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Vendor_Parameters(vendorInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_VENDOR.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateVendor(DataBase dataBase, DbTransaction tran, VendorInfo vendorInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    vendorInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Vendor_Parameters(vendorInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_VENDOR.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

